Hey all ! I'm on a quest to help the planner (on pg14) use the best of several partial, expressional indices we have on some large tables (few TBs in size, billions of records). As we know, stats for expressions in partial indices aren't gathered by default - so I'm tinkering with expressional extended stats to cover for those. I've tackled two interesting points there: 1. Seems like expressional stats involving the equality operator are skipped or mismatched (fiddle <https://www.db-fiddle.com/f/4jyoMCicNSZpjMt4jFYoz5/5379>) Let's take the following naive example: *create table t1 (x integer[]);insert into t1 select array[1]::integer[] from generate_series(1, 100000, 1);create statistics s1 on (x[1] = 1) from t1;analyze t1;* *explain analyze select * from t1 where x[1] = 1;* *> Seq Scan on t1 (cost=0.00..1986.00 rows=500 width=29) (actual time=0.009..36.035 rows=100000 loops=1)* Now, of course one can just create the stat on x[1] directly in this case, but I have a more complex use case where an equality operator is beneficial; After debugging it a bit - it seems that the root cause here is that we go through a flow where we only ever consider statistics for the lhs of the expression, and not the entire expression: clause_selectivity_ext -> restriction_selectivity -> eqsel_internal -> var_eq_const, where the vardata holds info about x[1]. The case expression goes through a slightly different flow (clause_selectivity_ext -> boolvarsel -> ...) and is matched on the entire expression. I wonder if it would make sense to first check for if there's a valid stat data on the expression in its entirety before jumping to the restriction selectivity on the variable itself, as there's nothing preventing users from defining such an extended statistic. The below naive implementation works, for instance (clearly, I'm not versed in the source code, this is for demonstration purposes only): --- src/backend/optimizer/path/clausesel.c | 20 +++++++++++++++----- 1 file changed, 15 insertions(+), 5 deletions(-) diff --git a/src/backend/optimizer/path/clausesel.c b/src/backend/optimizer/path/clausesel.c index 06f836308d..5e03d21dc0 100644 --- a/src/backend/optimizer/path/clausesel.c +++ b/src/backend/optimizer/path/clausesel.c @@ -871,11 +871,21 @@ clause_selectivity_ext(PlannerInfo *root, } else { - /* Estimate selectivity for a restriction clause. */ - s1 = restriction_selectivity(root, opno, - opclause->args, - opclause->inputcollid, - varRelid); + VariableStatData vardata; + + examine_variable(root, clause, varRelid, &vardata); + if (HeapTupleIsValid(vardata.statsTuple)) + { + /* Try estimating selectivity based on the entire expression first */ + s1 = boolvarsel(root, clause, varRelid); + } else { + /* There's no expressional statistic on the restriction clause - fallback to estimating restriction selectivity for the given node */ + s1 = restriction_selectivity(root, opno, + opclause->args, + opclause->inputcollid, + varRelid); + } + ReleaseVariableStats(vardata); } /* -- 2. Less important, just a minor note - feel free to ignore - although the eq. operator above seems to be skipped when matching the ext. stats, I can work around this by using a CASE expression (fiddle <https://www.db-fiddle.com/f/wJZNH1rNwJSo3D5aByQiWX/1>); Building on the above example, we can: *create statistics s2 on (case x[1] when 1 then true else false end) from t1;* *explain analyze select * from t1 where (case x[1] when 1 then true else false end* *> Seq Scan on t1 (cost=0.00..1986.00 rows=100000 width=25) (actual time=0.011..33.721 rows=100000 loops=1)* What's a bit problematic here, though, is that if we mix other dependent columns to the extended stat, and specifically if we create an mcv, queries involving the CASE expression throw with `error: unknown clause type 130`, where clause type == T_CaseExpr. The second point for me would be that I've found it a bit non intuitive that creating an extended statistic can fail queries at query time; it makes sense that the mcv wouldn't work for case expressions, but it might've been a bit clearer to: a. Fail this at statistic creation time, potentially, or b. Convert the type numeric in the above error to its text representation, if we can extract it out at runtime somehow - I couldn't find a mapping of clause type numerics to their names, and as the node tags are generated at compile time, it could be build-dependent and a bit hard to track down if one doesn't control the build flags Thanks a ton for your help - appreciate your time, Danny